package me.chyxion.dao.mybatis.pagination;

import java.sql.PreparedStatement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Oracle Or SQLServer Pagination Query
 * <pre>
 * select * from (
 *     select user_name, gender,
 *         row_number() over (order by birthdate desc, user_id asc) row_number__
 *     from  users) t 
 * where row_number__ >= 1 and row_number__ <= 16
 * </pre>
 * @version 0.0.1
 * @since 0.0.1
 * @author Shaun Chyxion <br />
 * chyxion@163.com <br />
 * Jan 14, 2015 5:36:45 PM
 */
public class DialectOracle extends DbDialect {
	private static final Logger log = LoggerFactory.getLogger(DialectOracle.class);
	public static final String COLUMN_ROW_NUMBER = "row_number__";

	@Override
	public String getSimplePageSQL(String targetSQL, PageParam<?> pageParam) {
		return getPageSQL(targetSQL, pageParam, false);
	}

	/* (non-Javadoc)
	 * @see me.chyxion.lawnation.dao.mybatis.DbDialect#getPagePreparedStagementSQL(java.lang.String, me.chyxion.lawnation.dao.PageParam)
	 */
	@Override
	public String getPreparedPageSQL(String targetSQL,
			PageParam<?> pageParam) {
		return getPageSQL(targetSQL, pageParam, true);
	}
	
	
	/**
	 * get page sql
	 * @param targetSQL
	 * @param pageParam
	 * @param prepare
	 * @return
	 */
	private String getPageSQL(String targetSQL, PageParam<?> pageParam, boolean prepare) {
		Object rowStart, rowEnd;
		if (prepare) {
			rowStart = rowEnd = "?";
		}
		else {
			rowStart = pageParam.getStart();
			rowEnd = pageParam.getStart() + pageParam.getLimit();
		}

		int fromIndex = indexOfFrom(targetSQL);
		StringBuffer sbSQL = 
			new StringBuffer("select * from (")
			.append(targetSQL.substring(0, fromIndex))
			.append(", row_number() over (order by ")
			.append(buildOrderBy(pageParam))
			.append(") ")
			.append(COLUMN_ROW_NUMBER)
			.append(targetSQL.substring(fromIndex))
			.append(") t where ")
			.append(COLUMN_ROW_NUMBER)
			.append(" >= ")
			.append(rowStart)
			.append(" and ")
			.append(COLUMN_ROW_NUMBER)
			.append(" <= ")
			.append(rowEnd); 
		log.debug("Generate Page Query SQL: [{}].", sbSQL);
		return sbSQL.toString();
	}

	/*
	 * (non-Javadoc)
	 * @see me.chyxion.dao.mybatis.pagination.DbDialect#beforeQuery(java.sql.PreparedStatement, me.chyxion.dao.mybatis.pagination.PageParam, int)
	 */
	@Override
	public void beforeQuery(PreparedStatement ps, 
			PageParam<?> pageParam, 
			int originalArgsSize) throws Exception {
		ps.setInt(originalArgsSize + 1, pageParam.getStart());
		ps.setInt(originalArgsSize + 2, pageParam.getStart() + pageParam.getLimit());
	}
}
